package locacao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import util.Conexao;

public class RepositorioLocacaoBanco implements ILocacao{
	
	private Connection con;
	
	@Override
	public void adicionar(Locacao locacao) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "insert into locacao (\"dataInicial\", \"dataEntrega\" ,\"totalParcial\", status, \"idCliente\", \"idFuncionario\")"+
			 "	values (?,?,?,?,?,?);";
		PreparedStatement ps = con.prepareStatement(sql);
		
		ps.setDate(1,  locacao.getDataInicial());
		ps.setDate(2,	locacao.getDataEntrega());
		ps.setDouble(3, locacao.getTotalParcial());
		ps.setInt(4, locacao.getStatus());
		ps.setInt(5, locacao.getCliente().getId());
		ps.setInt(6, locacao.getFuncioanario().getId());
		ps.execute();
	}

	@Override
	public void editar(Locacao locacao) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "update locacao set \"dataInicial\"= ?, \"dataEntrega\" = ?,\"totalParcial\" = ?,"+
				" status = ?, \"idCliente\" = ?, \"idFuncionario\" = ? where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setDate(1, (Date) locacao.getDataInicial());
		ps.setDate(2, (Date) locacao.getDataEntrega());
		ps.setDouble(3, locacao.getTotalParcial());
		ps.setInt(4, locacao.getStatus());
		ps.setInt(5, locacao.getCliente().getId());
		ps.setInt(6, locacao.getFuncioanario().getId());
		ps.setInt(7, locacao.getId());
		ps.execute();
	}

	@Override
	public void remover(int idLocacao) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "delete from locacao where id = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idLocacao);
		ps.execute();
	}

	@Override
	public void entregar(Locacao loc) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		con = Conexao.pegarConexao();
		String sql = "update locacao set status = 1 where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, loc.getId());
		ps.execute();
		
	}

	@Override
	public int existe(int idLocacao) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		int res = 0;
		String sql = "select * from locacao where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idLocacao);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		if(rs.next()){
			res = 1;
		}
		return res;
	}

	@Override
	public Locacao pesquisar(int idLocacao) throws ClassNotFoundException, SQLException {
		String sql = "select * from locacao where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idLocacao);
		ps.execute();
		Locacao loc = null;
		ResultSet rs = ps.getResultSet();
		if(rs.next()){
			loc = new Locacao();
			loc.setId(rs.getInt("id"));
			loc.setDataInicial(rs.getDate("dataInicial"));
			loc.setDataEntrega(rs.getDate("dataEntrega"));
			loc.setTotalParcial(rs.getDouble("totalParcial"));
			loc.setStatus(rs.getInt("status"));
			loc.getCliente().setId(rs.getInt("idCliente"));
			loc.getFuncioanario().setId(rs.getInt("idFuncionario"));
		}
		return loc;
	}

	@Override
	public ArrayList<Locacao> listar(int status) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		PreparedStatement ps;
		if(status > -1){
			String sql = "select * from locacao where status = ?;";
			ps = con.prepareStatement(sql);
			ps.setInt(1, status);
		}else{
			String sql = "select * from locacao;";
			ps = con.prepareStatement(sql);
		}
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Locacao> locs = new ArrayList<Locacao>();
		while(rs.next()){
			Locacao loc = new Locacao();
			loc.setId(rs.getInt("id"));
			loc.setDataInicial(rs.getDate("dataInicial"));
			loc.setDataEntrega(rs.getDate("dataEntrega"));
			loc.setTotalParcial(rs.getDouble("totalParcial"));
			loc.setStatus(rs.getInt("status"));
			loc.getCliente().setId(rs.getInt("idCliente"));
			loc.getFuncioanario().setId(rs.getInt("idFuncionario"));
			locs.add(loc);
		}
		return  locs;
	}

	
	@Override
	public ArrayList<Locacao> listarPorCliente(String nomeCliente, int status) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		PreparedStatement ps;
		if(status > -1){
			String sql = "select l.*  from locacao l, cliente c where l.\"idCliente\" = c.id"+ 
					" and lower(c.nome) like lower(?) || '%' and l.status = ?;";
			ps = con.prepareStatement(sql);
			ps.setString(1, nomeCliente);
			ps.setInt(2, status);
		}else{
			String sql = "select l.*  from locacao l, cliente c where l.\"idCliente\" = c.id"+ 
					" and lower(c.nome) like lower(?) || '%';";
			ps = con.prepareStatement(sql);
			ps.setString(1, nomeCliente);
		}
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Locacao> locs = new ArrayList<Locacao>();
		while(rs.next()){
			Locacao loc = new Locacao();
			loc.setId(rs.getInt("id"));
			loc.setDataInicial(rs.getDate("dataInicial"));
			loc.setDataEntrega(rs.getDate("dataEntrega"));
			loc.setTotalParcial(rs.getDouble("totalParcial"));
			loc.setStatus(rs.getInt("status"));
			loc.getCliente().setId(rs.getInt("idCliente"));
			loc.getFuncioanario().setId(rs.getInt("idFuncionario"));
			locs.add(loc);
		}
		return locs;
	}

	@Override
	public ArrayList<Locacao> listarPorFuncionario(String nomeFuncionario,
			int status) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		PreparedStatement ps;
		if(status > -1){
			String sql = "select l.*  from locacao l, funcionario f where l.\"idFuncionario\" = f.id" +
					" and lower(f.nome) like lower(?) || '%' and l.status = ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, nomeFuncionario);
			ps.setInt(2, status);
		}else{
			String sql = "select l.*  from locacao l, funcionario f where l.\"idFuncionario\" = f.id"+ 
					" and lower(f.nome) like lower(?) || '%';";
			ps = con.prepareStatement(sql);
			ps.setString(1, nomeFuncionario);
		}
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Locacao> locs = new ArrayList<Locacao>();
		while(rs.next()){
			Locacao loc = new Locacao();
			loc.setId(rs.getInt("id"));
			loc.setDataInicial(rs.getDate("dataInicial"));
			loc.setDataEntrega(rs.getDate("dataEntrega"));
			loc.setTotalParcial(rs.getDouble("totalParcial"));
			loc.setStatus(rs.getInt("status"));
			loc.getCliente().setId(rs.getInt("idCliente"));
			loc.getFuncioanario().setId(rs.getInt("idFuncionario"));
			locs.add(loc);
		}
		return locs;
	}

	@Override
	public int getNovoIdLocacao() throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		Statement st = con.createStatement();
		String sql = "select (last_value+1) as novo  from locacao_id_seq;;";
		ResultSet rs = st.executeQuery(sql);
		int numero = 0;
		if(rs.next()){
			numero = rs.getInt("novo");
		}
		return numero;
	}

	@Override
	public int locacaoTemFuncionario(int idFuncionario) throws ClassNotFoundException, SQLException {
		int res = 0;
		String sql = "select * from funcionario where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idFuncionario);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		if(rs.next()){
			res = 1;
		}
		return res;
	}

	@Override
	public int locacaoTemCliente(int idCliente) throws ClassNotFoundException, SQLException {
		int res = 0;
		String sql = "select * from cliente where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idCliente);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		if(rs.next()){
			res = 1;
		}
		return res;
	}

	@Override
	public int locacaoJaEntregue(int idLocacao) throws ClassNotFoundException, SQLException {
		int res = 0;
		String sql = "select * from locacao where id = ? and status = 1;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idLocacao);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		if(rs.next()){
			res = 1;
		}
		return res;
	}

}
